﻿using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Wicresoft.UnifyShow.Business;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class ReportViewManage : Basic, IReportView
    {

        private string GetMySelectedColumns = "select a.*,b.NAME as NAME,b.FIELD as FIELD from REPORTVIEW_USERSET a left join REPORTVIEW_COLUMN b on a.columnid = b.id where a.userid ={0} and b.querytype='{1}' and b.isvalid=1 order by b.id";
        private string DeleteMySelectedColumns = "delete from REPORTVIEW_USERSET where userid=:userID and querytype=:QueryType";

        /// <summary>
        /// 获取用户选择的列
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public List<ReportViewUserSet> GetDataColumnByUserID(decimal UserID, string QueryType)
        {
            var sql = string.Format(GetMySelectedColumns, UserID, QueryType);
            var list = ModelHelper<ReportViewUserSet>.GetModel_By_DirectSql(sql, null, oda);

            if (list == null || list.Count == 0)
            {
                list = new List<ReportViewUserSet>();
                var columns = GetAllColumnsByQueryType(QueryType);

                foreach (var column in columns)
                {
                    list.Add(new ReportViewUserSet()
                    {
                        QueryType = column.QueryType,
                        Name = column.Name,
                        Field = column.Field,
                        IsSelected = 1,
                        ColumnID = column.ID
                    });
                }
            }

            return list;
        }

        /// <summary>
        /// 获取某种查询的所有列
        /// </summary>
        /// <param name="QueryType"></param>
        /// <returns></returns>
        public List<ReportViewColumn> GetAllColumnsByQueryType(string QueryType, bool usingDefault = true)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){
                    OracleDbColumnName="QueryType", 
                    OracleDbColumnType=OracleDbType.NVarchar2,
                    OracleDbColumnValue=QueryType,
                    QueryOperator=QueryOperatorType.equal,
                    PredicateType=PredicateType.where
                },

                new QueryParameter(){
                    OracleDbColumnName="IsValid", 
                    OracleDbColumnType=OracleDbType.Decimal,
                    OracleDbColumnValue=1,
                    QueryOperator=QueryOperatorType.equal,
                    PredicateType=PredicateType.where
                }
            };

            if (usingDefault)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "IsDefault",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = 1,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }

            return ModelHelper<ReportViewColumn>.GetModel_By_QueryParams(attParam, null, null, null, oda);
        }

        /// <summary>
        /// 更新我的选择
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="Columns"></param>
        public void UpdateSelectedColumns(decimal UserID, string QueryType, List<ReportViewUserSet> Columns)
        {
            //先删除原先的选择
            ModelHelper<ReportViewUserSet>.UpdateOrDeleteModel_By_DirectSql(DeleteMySelectedColumns, new List<OracleParameter>()
                {
                    new OracleParameter(){ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=UserID},
                    new OracleParameter(){ParameterName="QueryType",OracleDbType = OracleDbType.NVarchar2,Value=QueryType}
                }, oda);

            Columns.ForEach(x => { x.UserID = UserID; x.QueryType = QueryType; });
            ModelHelper<ReportViewUserSet>.AddListModel_By_QueryParams(Columns, new List<string>() { "ID", "Name", "Field" }, oda);
        }

        /// <summary>
        /// 获取事件性质
        /// </summary>
        /// <returns></returns>
        public List<string> GetEventProperties()
        {
            var sql = "select distinct (Event_Properties) from FJ_HPD_Help_Desk_EventType where cInt_Disabled!=1";
            var list = new List<string>();
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    while (dr.Read())
                    {
                        list.Add(dr["Event_Properties"].ToString());
                    }
                    dr.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 依据事件性质得到事件分类
        /// </summary>
        /// <param name="eventPorperty"></param>
        /// <returns></returns>
        public List<string> GetEventType1AccordingEventProperty(string eventPorperty)
        {
            var sql = string.Format("select distinct(Tier1) from FJ_HPD_Help_Desk_EventType where cInt_Disabled!=1 and Event_Properties='{0}' and tier1!='其他'", eventPorperty);
            var list = new List<string>();
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    while (dr.Read())
                    {
                        list.Add(dr["Tier1"].ToString());
                    }
                    dr.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 获取合并工单中公司对应的二级单位
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public List<string> GetDepartmentListWithCompany(string company)
        {
            var sql = @"
                        select distinct case  when Contact_company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when Contact_company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when Contact_company='福建省烟草专卖局' and b.department = '省局机关运维' then '省局机关运维' when Contact_company!='福建省烟草专卖局' and b.department not in('中软海晟技术支持组','省局机关运维')  and b.company!='福建省烟草专卖局' then '信息中心' when Contact_company!='福建省烟草专卖局' and b.department in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department )end as Department from hpd_help_desk a left join user_x@padmin b on a.assignee_login_id = b.name where Assignee is not null and b.department is not null and contact_company='{c}'
                        union
                        select distinct case when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' when a.company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company!='福建省烟草专卖局' then '信息中心' when a.company!='福建省烟草专卖局' and b.department in('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department) end  as Department from CHG_Infrastructure_Change a left join user_x@padmin b on a.aslogid=b.name  where b.department is not null and a.company='{c}'
                        union
                        select distinct case when c.name !='福建省烟草专卖局' and  d.department not in ('中软海晟技术支持组','省局机关运维','省局信息中心') and d.company!='福建省烟草专卖局' then '信息中心' when c.name !='福建省烟草专卖局' and  d.department in ('中软海晟技术支持组','省局机关运维')  then '厦门海晟技术支持组' when c.name = '福建省烟草专卖局' and  d.department='中软海晟技术支持组' and d.company='福建省烟草专卖局' then '厦门海晟技术支持组' when c.name = '福建省烟草专卖局' and  d.department='经济信息中心' then '省局信息中心' when c.name = '福建省烟草专卖局' and d.department='省局机关运维' and d.company='福建省烟草专卖局' then '省局机关运维' else to_char(d.department) end as Department from taskview a left join sitebranch@padmin c on a.location = c.id left join user_x@padmin d on d.id = a.ownerid where d.department is not null and c.name='{c}'
                        union
                        select distinct case when a.Company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company!='福建省烟草专卖局' then '信息中心' when a.Company!='福建省烟草专卖局' and b.department  in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' else to_char(b.department) end as Department from PBM_PROBLEM_INVESTIGATION a left join user_x@padmin b on a.assignee_login_id = b.name where Assignee is not null and b.department is not null and contact_company='{c}'
                        union
                        select distinct case when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' when a.Company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company != '福建省烟草专卖局' then '信息中心' when a.Company!='福建省烟草专卖局' and b.department in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department) end as Department from RKM_KnowledgeArticleManager a left join user_x@padmin b on a.submitter = b.name where b.department is not null and a.company='{c}'";

            sql = sql.Replace("{c}", company);
            var list = new List<string>();
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    while (dr.Read())
                    {
                        list.Add(dr["Department"].ToString());
                    }
                    dr.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 合并工单搜索
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<WorkOrderView> SearchWorkOrderList(WorkOrderQueryString query, string sort, string order)
        {
            if (!query.QueryEvent && !query.QueryChange && !query.QueryTask && !query.QueryQuestion && !query.QueryKnowledge) return null;

            var eventSql = "(select {t},count(*) as Event_Count,sum(case when (status = 4 or status = 5 ) then 1 else 0 end) as Event_ResolveCount,greatest(round(sum(case when status in (4,5) then Last_Resolved_Date_Val-Submit_Date_Val else 0 end )/3600/sum(case when status in (4,5) then 1 else 0 end),2),0) as Event_ResolveTime,round(sum(case when (status = 4 or status=5 ) then 1 else 0 end)/count(*) * 100,2) as Event_ResolveEfficiency,round(sum(SatisfyLevel)/(sum(case when SatisfyLevel is not null then 1 else 0 end )*5)*100,2) as Event_ResolveSatisfaction from ( select * from ( {0} ) {1} )  group by {t} ) A ";
            var taskSql = "(select {t},count(*) as Task_Count,sum(case when (status=2 or status=4 ) then 1 else 0 end ) as Task_CompletedCount,round(sum(case when (status=2 or status=4 ) then 1 else 0 end )/count(*),4)*100 as Task_ResolveEfficiency,round(sum(case when ( status in (2,4) and overflag = 0 ) then 1 else 0 end )/count(*),4)*100  as  Task_InTimeEfficiency from ( select * from ( {0} ) {1} ) group by {t} ) B ";
            var questionSql = "(select {t},count(*) as Question_Count,sum(case when Investigation_Status=8 then 1 else 0 end ) as Question_ClosedCount,sum(case when (Investigation_Status!=8 and Investigation_Status!=6 ) then 1 else 0 end ) as Question_InCompleteCount from ( select * from ({0} ) {1} ) group by {t} ) C ";
            var changeSql = "(select {t},count(*) as Change_Count,sum(case when (Change_Request_Status = 10 or Change_Request_Status=11) then 1 else 0 end) as Change_ResolveCount,round(sum(case when Change_Request_Status = 11 and (cChr_EndCode = 0 or cChr_EndCode = 1) then 1 else 0 end )/greatest(sum(case when Change_Request_Status = 11 then 1 else 0 end),1)*100,2) as Change_SuccessEfficiency,round(sum(case when (Change_Request_Status = 10 or Change_Request_Status=11) then 1 else 0 end)/count(*)*100,2) as Change_ResolveEfficiency,round(sum(case when Actual_End_Date is not null and Actual_End_Date-Scheduled_End_Date > 0 then 1 else 0 end )/count(*)*100,2) as Change_InTimeEfficiency from ( select * from ( {0} ) {1} ) group by {t} ) D ";
            var knowledgeSql = "(select {t},count(*) as Knowledge_Count from ( select * from ({0}) {1} ) group by {t} ) E ";

            var nvlName = "";
            var nvlDepartment = "";
            decimal nvlCount = 0;
            var nvlEventCount = "";
            var nvlTaskCount = "";
            var nvlQuestionCount = "";
            var nvlChangeCount = "";
            var nvlKnowledgeCount = "";

            var fulljoin = ""; var mainSqlTip = "";

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }
            var orderCondition = " order by " + sort + " " + orderName;

            var sql = "select * from ( select {0} from {1} ) {2} {3}";

            //事件工单
            if (query.QueryEvent)
            {
                eventSql = eventSql.Replace("{t}", query.GroupType == "Name" ? "Department,Name" : "Department");
                var eventBasicSql = "select Assignee as Name,Contact_Company,Status,SatisfyLevel,case when Contact_company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when Contact_company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when Contact_company='福建省烟草专卖局' and b.department = '省局机关运维' then '省局机关运维' when Contact_company!='福建省烟草专卖局' and b.department not in('中软海晟技术支持组','省局机关运维')  and b.company!='福建省烟草专卖局' then '信息中心' when Contact_company!='福建省烟草专卖局' and b.department in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department )end as department,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Last_Resolved_Date+28800,'second') as Last_Resolved_Date,Last_Resolved_Date as Last_Resolved_Date_Val,Submit_Date as Submit_Date_Val,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as Submit_Date from hpd_help_desk a left join user_x@padmin b on a.assignee_login_id = b.name where  a.assignee_login_id is not null and Assignee is not null and Event_Properties!='日常工作'  ";
                var condition = "";
                if (!string.IsNullOrEmpty(query.Company))
                {
                    condition += " Contact_Company = '" + query.Company + "'";
                }

                if (!string.IsNullOrEmpty(query.Department))
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Department = '" + query.Department + "'";
                }

                if (query.Submit_Date_From.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (query.Submit_Date_To.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }

                eventSql = string.Format(eventSql, eventBasicSql, condition);
                mainSqlTip = "A";

                fulljoin += eventSql;

                nvlCount += 1;

                nvlDepartment = generateNVL(nvlDepartment, "Department", nvlCount, "A");
                if (query.GroupType == "Name")
                {
                    nvlName = generateNVL(nvlName, "Name", nvlCount, "A");
                }

                nvlEventCount = "nvl(Event_Count,0) as Event_Count,nvl(Event_ResolveCount,0) as Event_ResolveCount,nvl(Event_ResolveTime,0 ) as Event_ResolveTime,nvl(Event_ResolveEfficiency,0) as Event_ResolveEfficiency,nvl(Event_ResolveSatisfaction,0) as Event_ResolveSatisfaction,";
            }

            //变更工单
            if (query.QueryChange)
            {
                changeSql = changeSql.Replace("{t}", query.GroupType == "Name" ? "Department,Name" : "Department");

                var changeBasicSql = "select ASCHG as Name,case when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' when a.company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company!='福建省烟草专卖局' then '信息中心' when a.company!='福建省烟草专卖局' and b.department in('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department) end  as Department,Change_Request_Status,cChr_EndCode,a.Company,Scheduled_End_Date,Actual_End_Date,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as Submit_Date from CHG_Infrastructure_Change a left join user_x@padmin b on a.aslogid=b.name  where a.aslogid is not null ";

                var condition = "";
                if (!string.IsNullOrEmpty(query.Company))
                {
                    condition += " Company = '" + query.Company + "'";
                }

                if (!string.IsNullOrEmpty(query.Department))
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Department = '" + query.Department + "'";
                }

                if (query.Submit_Date_From.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (query.Submit_Date_To.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }

                changeSql = string.Format(changeSql, changeBasicSql, condition);

                if (!string.IsNullOrEmpty(mainSqlTip))
                {
                    fulljoin += " full join " + changeSql + " on " + mainSqlTip + ".Department = D.Department ";
                    if (query.GroupType == "Name")
                    {
                        fulljoin += " and " + mainSqlTip + ".Name = D.Name";
                    }
                }
                else
                {
                    mainSqlTip = "D";
                    fulljoin = changeSql;
                }

                nvlCount += 1;
                nvlDepartment = generateNVL(nvlDepartment, "Department", nvlCount, "D");
                if (query.GroupType == "Name")
                {
                    nvlName = generateNVL(nvlName, "Name", nvlCount, "D");
                }

                nvlChangeCount = " nvl(Change_Count,0) as Change_Count,nvl(Change_ResolveCount,0) as Change_ResolveCount,nvl(Change_SuccessEfficiency,0) as Change_SuccessEfficiency,nvl(Change_ResolveEfficiency,0) as Change_ResolveEfficiency,nvl(Change_InTimeEfficiency,0) as Change_InTimeEfficiency,";
            }

            //计划工单
            if (query.QueryTask)
            {
                taskSql = taskSql.Replace("{t}", query.GroupType == "Name" ? "Department,Name" : "Department");

                var taskBasicSql = "select d.displayName as Name,case when c.name !='福建省烟草专卖局' and  d.department not in ('中软海晟技术支持组','省局机关运维','信息中心') and d.company!='福建省烟草专卖局' then '信息中心' when c.name !='福建省烟草专卖局' and  d.department in ('中软海晟技术支持组','省局机关运维')  then '厦门海晟技术支持组' when c.name = '福建省烟草专卖局' and  d.department='中软海晟技术支持组' and d.company='福建省烟草专卖局' then '厦门海晟技术支持组' when c.name = '福建省烟草专卖局' and  d.department='经济信息中心' then '省局信息中心' when c.name = '福建省烟草专卖局' and d.department='省局机关运维' and d.company='福建省烟草专卖局' then '省局机关运维' else to_char(d.department) end as Department,c.name as Company ,a.createtime,a.ownerid ,a.status,a.overflag,a.enddate,a.completetime from taskview a left join sitebranch@padmin c on a.location = c.id left join user_x@padmin d on d.id = a.ownerid where a.ownerid is not null ";

                var condition = "";
                if (!string.IsNullOrEmpty(query.Company))
                {
                    condition += " Company = '" + query.Company + "'";
                }

                if (!string.IsNullOrEmpty(query.Department))
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Department = '" + query.Department + "'";
                }

                if (query.Submit_Date_From.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " createtime >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (query.Submit_Date_To.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " createtime < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }

                taskSql = string.Format(taskSql, taskBasicSql, condition);

                if (!string.IsNullOrEmpty(mainSqlTip))
                {
                    fulljoin += " full join " + taskSql + " on " + mainSqlTip + ".Department = B.Department ";
                    if (query.GroupType == "Name")
                    {
                        fulljoin += " and " + mainSqlTip + ".Name = B.Name";
                    }
                }
                else
                {
                    mainSqlTip = "B";
                    fulljoin = taskSql;
                }

                nvlCount++;
                nvlDepartment = generateNVL(nvlDepartment, "Department", nvlCount, "B");
                if (query.GroupType == "Name")
                {
                    nvlName = generateNVL(nvlName, "Name", nvlCount, "B");
                }

                nvlTaskCount = "nvl(Task_Count,0) as Task_Count,nvl(Task_CompletedCount,0) as Task_CompletedCount,nvl(Task_ResolveEfficiency,0) as Task_ResolveEfficiency,nvl(Task_InTimeEfficiency,0) as Task_InTimeEfficiency,";
            }

            //问题工单
            if (query.QueryQuestion)
            {
                questionSql = questionSql.Replace("{t}", query.GroupType == "Name" ? "Department,Name" : "Department");

                var questionBasicSql = "select Assignee as Name,case when a.Company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company!='福建省烟草专卖局' then '信息中心' when a.Company!='福建省烟草专卖局' and b.department  in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' else to_char(b.department) end as Department,a.Company,Investigation_Status,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as Submit_Date from PBM_PROBLEM_INVESTIGATION a left join user_x@padmin b on a.assignee_login_id = b.name where Assignee is not null  ";
                var condition = "";
                if (!string.IsNullOrEmpty(query.Company))
                {
                    condition += " Company = '" + query.Company + "'";
                }

                if (!string.IsNullOrEmpty(query.Department))
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Department = '" + query.Department + "'";
                }

                if (query.Submit_Date_From.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (query.Submit_Date_To.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }

                questionSql = string.Format(questionSql, questionBasicSql, condition);

                if (!string.IsNullOrEmpty(mainSqlTip))
                {
                    fulljoin += " full join " + questionSql + " on " + mainSqlTip + ".Department = C.Department ";
                    if (query.GroupType == "Name")
                    {
                        fulljoin += " and " + mainSqlTip + ".Name = C.Name";
                    }
                }
                else
                {
                    mainSqlTip = "C";
                    fulljoin = questionSql;
                }

                nvlCount++;
                nvlDepartment = generateNVL(nvlDepartment, "Department", nvlCount, "C");
                if (query.GroupType == "Name")
                {
                    nvlName = generateNVL(nvlName, "Name", nvlCount, "C");
                }

                nvlQuestionCount = "nvl(Question_Count,0) as Question_Count,nvl(Question_ClosedCount,0) as Question_ClosedCount,nvl(Question_InCompleteCount,0) as Question_InCompleteCount,";
            }

            //知识库工单
            if (query.QueryKnowledge)
            {
                knowledgeSql = knowledgeSql.Replace("{t}", query.GroupType == "Name" ? "Department,Name" : "Department");

                var basicKnowSql = "select cChr_SubmitterFullName as Name,case when a.company='福建省烟草专卖局' and b.department='中软海晟技术支持组' then '厦门海晟技术支持组' when a.company='福建省烟草专卖局' and b.department='经济信息中心' then '省局信息中心' when a.company='福建省烟草专卖局' and b.department='省局机关运维' then '省局机关运维' when a.Company!='福建省烟草专卖局' and b.department not in ('中软海晟技术支持组','省局机关运维') and b.company != '福建省烟草专卖局' then '信息中心' when a.Company!='福建省烟草专卖局' and b.department in ('中软海晟技术支持组','省局机关运维') then '厦门海晟技术支持组' else to_char(b.department) end as Department,a.Company,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Create_Date+28800,'second') as Submit_Date from RKM_KnowledgeArticleManager a left join user_x@padmin b on a.submitter = b.name  where a.submitter is not null ";

                var condition = "";
                if (!string.IsNullOrEmpty(query.Company))
                {
                    condition += " Company = '" + query.Company + "'";
                }

                if (!string.IsNullOrEmpty(query.Department))
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Department = '" + query.Department + "'";
                }

                if (query.Submit_Date_From.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (query.Submit_Date_To.HasValue)
                {
                    condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
                }

                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }

                knowledgeSql = string.Format(knowledgeSql, basicKnowSql, condition);

                if (!string.IsNullOrEmpty(mainSqlTip))
                {
                    fulljoin += " full join " + knowledgeSql + " on " + mainSqlTip + ".Department = E.Department ";
                    if (query.GroupType == "Name")
                    {
                        fulljoin += " and " + mainSqlTip + ".Name = E.Name";
                    }
                }
                else
                {
                    mainSqlTip = "E";
                    fulljoin = knowledgeSql;
                }

                nvlCount++;
                nvlDepartment = generateNVL(nvlDepartment, "Department", nvlCount, "E");
                if (query.GroupType == "Name")
                {
                    nvlName = generateNVL(nvlName, "Name", nvlCount, "E");
                }

                nvlKnowledgeCount = "nvl(Knowledge_Count,0) as Knowledge_Count,";
            }

            //只有一个要追加结束标识
            if (nvlCount == 1)
            {
                if (!string.IsNullOrEmpty(nvlName)) nvlName += "'')";
                if (!string.IsNullOrEmpty(nvlDepartment)) nvlDepartment += "'')";
            }

            if (!string.IsNullOrEmpty(nvlName))
            {
                nvlName = nvlName.TrimEnd(',') + " as Name,";
            }

            if (!string.IsNullOrEmpty(nvlDepartment))
            {
                nvlDepartment = nvlDepartment.TrimEnd(',') + " as Department, ";
            }

            var selectColumn = (nvlName + nvlDepartment + nvlEventCount + nvlTaskCount + nvlQuestionCount + nvlChangeCount + nvlKnowledgeCount).TrimEnd(',');


            if (!string.IsNullOrEmpty(query.Department))
            {
                sql = string.Format(sql, selectColumn, fulljoin, " where Department = '" + query.Department + "' ", orderCondition);
            }
            else
            {
                sql = string.Format(sql, selectColumn, fulljoin, " where Department is not null ", orderCondition);
            }

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<WorkOrderView>.GetModel_By_DirectSql(sql, null, conitsm).ToList();

                return list;
            }
        }

        #region 事件
        /// <summary>
        /// 分组统计事件列表
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<EventView> GroupEventList(decimal UserID, EventQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<EventView>();

            var columns = GetDataColumnByUserID(UserID, "事件综合");
            var allColumns = GetAllColumnsByQueryType("事件综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "Assignee";            //默认按解决人分组
            var groupName = "受派人";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from hpd_help_desk ";

            var condition = generateSqlCondition<EventQueryString>(query, new List<string>() { 
                "QueryType","Submit_Date_From","Submit_Date_To","Last_Resolved_Date_From","Last_Resolved_Date_To","Description"
            });

            if (!string.IsNullOrEmpty(query.Description))
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " (Description like '%" + query.Description + "%' or Detailed_Decription like '%" + query.Description + "%') ";
            }

            if (query.Submit_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Submit_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date >= to_date('" + query.Last_Resolved_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date < to_date('" + query.Last_Resolved_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, EventView>(dr, new List<string>() { groupCode, "Count" });
                    dr.Close();
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;

            return result;
        }

        private string generateEventSql(EventQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "Incident_Number", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("事件综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from hpd_help_desk";

            var condition = generateSqlCondition<EventQueryString>(query, new List<string>() { 
                "QueryType","Submit_Date_From","Submit_Date_To","Last_Resolved_Date_From","Last_Resolved_Date_To","Description"
            });

            if (!string.IsNullOrEmpty(query.Description))
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " (Description like '%" + query.Description + "%' or Detailed_Decription like '%" + query.Description + "%') ";
            }

            if (query.Submit_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Submit_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date >= to_date('" + query.Last_Resolved_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date < to_date('" + query.Last_Resolved_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }
            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);
        }

        /// <summary>
        /// 搜索事件列表
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<EventView> SearchEventList(EventQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "Incident_Number", string order = "Descending")
        {

            var countSql = "";
            var sql = generateEventSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<EventView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<EventView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        public DataTable SearchEventList_Report(EventQueryString query, int? pageIndex, int? pageSize, string sort = "Incident_Number", string order = "Descending")
        {
            var countSql = "";
            var sql = generateEventSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 变更
        /// <summary>
        /// 变更分组统计
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="groupName"></param>
        /// <param name="groupCode"></param>
        /// <returns></returns>
        public List<ChangeView> GroupChangeList(decimal UserID, ChangeQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<ChangeView>();

            var columns = GetDataColumnByUserID(UserID, "变更综合");
            var allColumns = GetAllColumnsByQueryType("变更综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "cChr_SubmitterFullName";            //默认按变更提交人分组
            var groupName = "变更提交人";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from CHG_Infrastructure_Change";

            var condition = generateSqlCondition<ChangeQueryString>(query, new List<string>() { 
                "QueryType","Submit_Date_From","Submit_Date_To","Last_Resolved_Date_From","Last_Resolved_Date_To","Description"
            });

            if (!string.IsNullOrEmpty(query.Description))
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " (Description like '%" + query.Description + "%' or Detailed_Description like '%" + query.Description + "%') ";
            }

            if (query.Submit_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Submit_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date >= to_date('" + query.Last_Resolved_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date < to_date('" + query.Last_Resolved_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, ChangeView>(dr, new List<string>() { groupCode, "Count" });
                    dr.Close();
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;

            return result;
        }

        private string generateChangeSql(ChangeQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "Infrastructure_Change_ID", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("变更综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from CHG_Infrastructure_Change";

            var condition = generateSqlCondition<ChangeQueryString>(query, new List<string>() { 
                "QueryType","Submit_Date_From","Submit_Date_To","Last_Resolved_Date_From","Last_Resolved_Date_To","Description"
            });

            if (!string.IsNullOrEmpty(query.Description))
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " (Description like '%" + query.Description + "%' or Detailed_Description like '%" + query.Description + "%') ";
            }

            if (query.Submit_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date >= to_date('" + query.Submit_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Submit_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Submit_Date < to_date('" + query.Submit_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date >= to_date('" + query.Last_Resolved_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Last_Resolved_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Last_Resolved_Date < to_date('" + query.Last_Resolved_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);
            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);
        }
        /// <summary>
        /// 变更搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<ChangeView> SearchChangeList(ChangeQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "Infrastructure_Change_ID", string order = "Descending")
        {
            var countSql = "";
            var sql = generateChangeSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<ChangeView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<ChangeView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        public DataTable SearchChangeList_Report(ChangeQueryString query, int? pageIndex, int? pageSize, string sort = "Infrastructure_Change_ID", string order = "Descending")
        {
            var countSql = "";
            var sql = generateChangeSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 任务
        /// <summary>
        /// 任务分组统计
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="GroupName"></param>
        /// <param name="GroupCode"></param>
        /// <returns></returns>
        public List<TaskView> GroupTaskList(decimal UserID, TaskQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<TaskView>();

            var columns = GetDataColumnByUserID(UserID, "任务综合");
            var allColumns = GetAllColumnsByQueryType("任务综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "OwnerUserName";            //默认按解决人分组
            var groupName = "责任人";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from taskview a left join user_x@padmin b on createuserid = b.id left join user_x@padmin c on ownerid = c.id left join sitebranch@padmin d on a.location = d.id";

            var condition = generateSqlCondition<TaskQueryString>(query, new List<string>() { 
                "QueryType","CreateTime_From","CreateTime_To","CompleteTime_From","CompleteTime_To","EndDate_From","EndDate_To"
            });

            if (query.CreateTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime >= to_date('" + query.CreateTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CreateTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime < to_date('" + query.CreateTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CompleteTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CompleteTime >= to_date('" + query.CompleteTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CompleteTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CompleteTime < to_date('" + query.CompleteTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.EndDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " EndDate >= to_date('" + query.EndDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.EndDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " EndDate < to_date('" + query.EndDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                result = ModelHelper<TaskView>.GetModel_By_DirectSql(sql, null, conitsm, new List<string>() { groupCode, "Count" });
            }


            GroupCode = groupCode;
            GroupName = groupName;

            return result;

        }

        private string generateTaskSql(TaskQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "JobNo", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("任务综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from taskview a left join user_x@padmin b on createuserid = b.id left join user_x@padmin c on ownerid = c.id left join sitebranch@padmin d on a.location = d.id";

            var condition = generateSqlCondition<TaskQueryString>(query, new List<string>() { 
                "QueryType","CreateTime_From","CreateTime_To","CompleteTime_From","CompleteTime_To","EndDate_From","EndDate_To"
            });

            if (query.CreateTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime >= to_date('" + query.CreateTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CreateTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime < to_date('" + query.CreateTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CompleteTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CompleteTime >= to_date('" + query.CompleteTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CompleteTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CompleteTime < to_date('" + query.CompleteTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.EndDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " EndDate >= to_date('" + query.EndDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.EndDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " EndDate < to_date('" + query.EndDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);

        }
        /// <summary>
        /// 任务搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<TaskView> SearchTaskList(TaskQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "JobNo", string order = "Descending")
        {
            var countSql = "";
            var sql = generateTaskSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<TaskView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<TaskView>.GetModel_By_DirectSql(sql, null, conitsm);
            }

        }

        public DataTable SearchTaskList_Report(TaskQueryString query, int? pageIndex, int? pageSize, string sort = "JobNo", string order = "Descending")
        {
            var countSql = "";
            var sql = generateTaskSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 硬件
        /// <summary>
        /// 硬件资产分组
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="groupName"></param>
        /// <param name="groupCode"></param>
        /// <returns></returns>
        public List<HardwareView> GroupHardwareList(decimal UserID, HardwareQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<HardwareView>();

            var columns = GetDataColumnByUserID(UserID, "硬件综合");
            var allColumns = GetAllColumnsByQueryType("硬件综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "HDType";            //默认按硬件分类分组
            var groupName = "类别";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_Hardware where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<HardwareQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","BuyTime_From","BuyTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime >= to_date('" + query.BuyTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime < to_date('" + query.BuyTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, HardwareView>(dr, new List<string>() { groupCode, "Count" });
                    dr.Close();
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;

            return result;
        }

        private string generateHardwareSql(HardwareQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "Infrastructure_Change_ID", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("硬件综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_Hardware where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<HardwareQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","BuyTime_From","BuyTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime >= to_date('" + query.BuyTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime < to_date('" + query.BuyTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);
        }
        /// <summary>
        /// 硬件搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<HardwareView> SearchHardwareList(HardwareQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "Infrastructure_Change_ID", string order = "Descending")
        {
            var countSql = "";
            var sql = generateHardwareSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<HardwareView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<HardwareView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        public DataTable SearchHardwareList_Report(HardwareQueryString query, int? pageIndex, int? pageSize, string sort = "Name", string order = "Descending")
        {
            var countSql = "";
            var sql = generateHardwareSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 软件
        /// <summary>
        /// 软件分组统计
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="groupName"></param>
        /// <param name="groupCode"></param>
        /// <returns></returns>
        public List<SoftwareView> GroupSoftwareList(decimal UserID, SoftwareQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<SoftwareView>();

            var columns = GetDataColumnByUserID(UserID, "软件综合");
            var allColumns = GetAllColumnsByQueryType("软件综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "SoftType";            //默认按软件分类分组
            var groupName = "类别";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_SysSoftware where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<SoftwareQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","BuyTime_From","BuyTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime >= to_date('" + query.BuyTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime < to_date('" + query.BuyTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, SoftwareView>(dr, new List<string>() { groupCode, "Count" });
                    dr.Close();
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;

            return result;
        }

        private string generateSoftwareSql(SoftwareQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "Name", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("软件综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_SysSoftware where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<SoftwareQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","BuyTime_From","BuyTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime >= to_date('" + query.BuyTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.BuyTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " BuyTime < to_date('" + query.BuyTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);
        }

        /// <summary>
        /// 软件搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<SoftwareView> SearchSoftwareList(SoftwareQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "Name", string order = "Descending")
        {
            var countSql = "";
            var sql = generateSoftwareSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<SoftwareView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<SoftwareView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        public DataTable SearchSoftwareList_Report(SoftwareQueryString query, int? pageIndex, int? pageSize, string sort = "Name", string order = "Descending")
        {
            var countSql = "";
            var sql = generateSoftwareSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 应用系统
        /// <summary>
        /// 应用系统分组统计
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="groupName"></param>
        /// <param name="groupCode"></param>
        /// <returns></returns>
        public List<BusinessSystemView> GroupBusinessSystemList(decimal UserID, BusinessSystemQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<BusinessSystemView>();

            var columns = GetDataColumnByUserID(UserID, "业务系统综合");
            var allColumns = GetAllColumnsByQueryType("业务系统综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "BSType";            //默认按应用系统分类分组
            var groupName = "类别";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_Appsystem where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<BusinessSystemQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","FirstOnlineTime_From","FirstOnlineTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.FirstOnlineTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " FirstOnlineTime >= to_date('" + query.FirstOnlineTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.FirstOnlineTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " FirstOnlineTime < to_date('" + query.FirstOnlineTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(groupCode)) condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " " + groupCode + " is not null ";

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, BusinessSystemView>(dr, new List<string>() { groupCode, "Count" });
                    dr.Close();
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;

            return result;
        }

        private string generateBusinessSystemSql(BusinessSystemQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "Name", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("业务系统综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from AST_BMC_Appsystem where datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) ";

            var condition = generateSqlCondition<BusinessSystemQueryString>(query, new List<string>() { 
                "QueryType","Create_Date_From","Create_Date_To","FirstOnlineTime_From","FirstOnlineTime_To","ExpireTime_From","ExpireTime_To","DisposalDate_From","DisposalDate_To"
            });

            if (query.Create_Date_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date >= to_date('" + query.Create_Date_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.Create_Date_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " Create_Date < to_date('" + query.Create_Date_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.FirstOnlineTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " FirstOnlineTime >= to_date('" + query.FirstOnlineTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.FirstOnlineTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " FirstOnlineTime < to_date('" + query.FirstOnlineTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime >= to_date('" + query.ExpireTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.ExpireTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " ExpireTime < to_date('" + query.ExpireTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate >= to_date('" + query.DisposalDate_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.DisposalDate_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " DisposalDate < to_date('" + query.DisposalDate_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return generateSql(basicSql, condition, order, sort, pageIndex, pageSize);
        }

        /// <summary>
        /// 应用系统搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<BusinessSystemView> SearchBusinessSystemList(BusinessSystemQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "Name", string order = "Descending")
        {
            var countSql = "";
            var sql = generateBusinessSystemSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                totalCount = ModelHelper<BusinessSystemView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<BusinessSystemView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        public DataTable SearchBusinessSystemList_Report(BusinessSystemQueryString query, int? pageIndex, int? pageSize, string sort = "Name", string order = "Descending")
        {
            var countSql = "";
            var sql = generateBusinessSystemSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }
        }

        #endregion

        #region 告警
        /// <summary>
        /// 分组统计告警
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="groupName"></param>
        /// <param name="groupCode"></param>
        /// <returns></returns>
        public List<AlertView> GroupAlertList(decimal UserID, AlertQueryString query, out string GroupName, out string GroupCode)
        {
            var result = new List<AlertView>();

            var columns = GetDataColumnByUserID(UserID, "告警综合");
            var allColumns = GetAllColumnsByQueryType("告警综合", false);
            var sql = "select {0},count(*) as Count from ({1}) {2} group by {3} order by Count desc";
            var groupCode = "Type";            //默认按应用系统分类分组
            var groupName = "告警类别";

            var group = columns.FirstOrDefault();
            if (group != null && !string.IsNullOrEmpty(group.GroupCode))
            {
                groupCode = group.GroupCode;
                groupName = group.GroupName;
            }

            var basicSql = generateBasicSql(allColumns, null) + " from eg_event a left join (select ip,item from MONITOR_SERVER union select ip,item from MONITOR_NETWORK) b on a.mc_Host_Address = b.ip where mc_origin_class in ('服务器','数据库','中间件','应用系统','网络','安全事件','机房','存储') ";


            var condition = generateSqlCondition<AlertQueryString>(query, new List<string>() { 
                    "QueryType","CalculateDate_From","CalculateDate_To","CreateTime_From","CreateTime_To","UseagePercent_From","UseagePercent_To"
                });


            if (query.CreateTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime >= to_date('" + query.CreateTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CreateTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime < to_date('" + query.CreateTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            if (groupCode == "Type")
            {
                sql = string.Format(sql, groupCode, basicSql, condition, groupCode);

                using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
                {
                    using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                    {
                        result = AutoMapper.Map<OracleDataReader, AlertView>(dr, new List<string>() { groupCode, "Count" });
                        dr.Close();
                    }
                }
            }
            //按告警对象分组要计算可用性
            else if (groupCode == "HostAddress")
            {
                sql = string.Format("select * from ({0}) {1} ", basicSql + " and Severity in ('CRITICAL','MAJOR')", condition);

                //0--sql
                //1--CalculateDate_From
                //2--CalculateDate_To
                var ipSql = @" select ip,item from MONITOR_SERVER union select ip,item from MONITOR_NETWORK ";
                var groupSql = @"select * from (
                                select HostAddress,count(*) as Count,round(max(ResolveTime)-min(Createtime)-nvl(sum(durationTime),0),8)*24 as BadWorkHours from (
                                select HostAddress,Createtime,ResolveTime,(case when (Createtime-LAG(ResolveTime)over(partition by HostAddress order by Createtime)) <= 0 then 0 else Createtime-LAG(ResolveTime)over(partition by HostAddress order by Createtime) end ) durationTime
                                from (select x.HostAddress,x.Createtime,y.ResolveTime from 
                                (select rownum rn,HostAddress,Createtime from(
                                select HostAddress,CreateTime from (
                                select 
                                HostAddress ,
                                least(greatest(CreateTime,to_date('{1}','yyyy-mm-dd')),to_date('{2}','yyyy-mm-dd')) as CreateTime
                                from ({0}) ) 
                                order by HostAddress,CreateTime) A) x,
                                (select rownum rn,HostAddress,ResolveTime from(
                                select HostAddress,ResolveTime from (
                                select 
                                HostAddress ,
                                greatest(least(nvl(ResolveTime ,sysdate),to_date('{2}','yyyy-mm-dd')),to_date('{1}','yyyy-mm-dd'))  as ResolveTime
                                from ({0}) ) 
                                order by HostAddress,ResolveTime) B) y
                                where x.HostAddress = y.HostAddress and x.rn = y.rn)
                                ) group by HostAddress )";

                var using_all_sql = !(
                    (!string.IsNullOrEmpty(query.CompanyName)&& query.CompanyName != "福建省烟草专卖局")
                    || !string.IsNullOrEmpty(query.Message)
                    || !string.IsNullOrEmpty(query.BusinessName)
                    || !string.IsNullOrEmpty(query.Severity)
                    || !string.IsNullOrEmpty(query.Status)
                    || !string.IsNullOrEmpty(query.HostAddress)
                );

                if (using_all_sql)
                {
                    groupSql = @"select a.ip as HostAddress,a.item as ItemName,nvl(b.count,0) as Count,nvl(b.badworkhours,0) as BadWorkHours from ({ipSql}) a left join ( {group} ) b on a.ip = b.hostaddress "
                        .Replace("{ipSql}", ipSql).Replace("{group}", groupSql);
                }
                else
                {
                    groupSql = @"select a.*,b.item as ItemName from ( {group} ) a left join ({ipSql}) b on a.hostaddress = b.ip"
                        .Replace("{ipSql}", ipSql).Replace("{group}", groupSql);
                }

                if (query.CalculateDate_From.HasValue && query.CalculateDate_To.HasValue)
                {
                    var TotalHours = Convert.ToDecimal((query.CalculateDate_To.Value.AddDays(1) - query.CalculateDate_From.Value).TotalHours);
                    sql = string.Format(groupSql, sql, query.CalculateDate_From.Value.ToString("yyyy-MM-dd"), query.CalculateDate_To.Value.AddDays(1).ToString("yyyy-MM-dd"));

                    using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
                    {
                        using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                        {
                            result = AutoMapper.Map<OracleDataReader, AlertView>(dr, new List<string>() { groupCode, "ItemName", "Count", "BadWorkHours" });
                            dr.Close();
                            result.ForEach(x =>
                            {
                                x.WorkTime = Math.Floor((TotalHours - x.BadWorkHours) * 100) / 100;
                                x.UsagePercentVal = Math.Floor((TotalHours - x.BadWorkHours) / TotalHours * 10000) / 100;
                                x.UsagePercent = x.UsagePercentVal + "%";
                            });

                            if (query.UseagePercent_From.HasValue)
                            {
                                result = result.Where(x => (double)x.UsagePercentVal >= query.UseagePercent_From.Value).ToList();
                            }

                            if (query.UseagePercent_To.HasValue)
                            {
                                result = result.Where(x => (double)x.UsagePercentVal <= query.UseagePercent_To.Value).ToList();
                            }

                        }
                    }
                }
            }

            GroupCode = groupCode;
            GroupName = groupName;
            return result;
        }

        /// <summary>
        /// 生成告警查询语句
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <param name="countSql"></param>
        /// <returns></returns>
        private string generateAlertSql(AlertQueryString query, int? pageIndex, int? pageSize, out string countSql, string sort = "CreateTime", string order = "Descending")
        {
            var allColumns = GetAllColumnsByQueryType("告警综合", false);

            var basicSql = generateBasicSql(allColumns, null) + " from eg_event a left join (select ip,item from MONITOR_SERVER union select ip,item from MONITOR_NETWORK) b on a.mc_Host_Address = b.ip  where mc_origin_class in ('服务器','数据库','中间件','应用系统','网络','安全事件','机房','存储') ";

            //以告警对象分组，统计无故障率，只筛选严重和主要的告警
            if (query.CalculateDate_From.HasValue && query.CalculateDate_To.HasValue)
            {
                basicSql = basicSql + " and Severity in ('CRITICAL','MAJOR') ";
            }

            var condition = generateSqlCondition<AlertQueryString>(query, new List<string>() { 
                    "QueryType","CalculateDate_From","CalculateDate_To","CreateTime_From","CreateTime_To","UseagePercent_From","UseagePercent_To"
                });

            if (query.CreateTime_From.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime >= to_date('" + query.CreateTime_From.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (query.CreateTime_To.HasValue)
            {
                condition += (!string.IsNullOrEmpty(condition) ? " and " : "") + " CreateTime < to_date('" + query.CreateTime_To.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            }

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " where " + condition;
            }

            var sql = generateSql(basicSql, condition, order, sort, pageIndex, pageSize);

            countSql = string.Format("select count(*) from ( {0} ) {1}", basicSql, condition);

            return sql;
        }
        /// <summary>
        /// 告警搜索
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<AlertView> SearchAlertList(AlertQueryString query, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "CreateTime", string order = "Descending")
        {
            var countSql = "";
            var sql = generateAlertSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
            {
                totalCount = ModelHelper<AlertView>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                return ModelHelper<AlertView>.GetModel_By_DirectSql(sql, null, conitsm);
            }
        }

        /// <summary>
        /// 告警前10000条用于excel导出
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public DataTable SearchAlertList_Export(AlertQueryString query, int? pageIndex, int? pageSize, string sort = "CreateTime", string order = "Descending")
        {
            var countSql = "";
            var sql = generateAlertSql(query, pageIndex, pageSize, out countSql, sort, order);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
            {
                return conitsm.ExecuteFillToDataTable(sql);
            }

        }

        #endregion

        #region helper
        /// <summary>
        /// 组建where查询条件
        /// </summary>
        /// <param name="query"></param>
        /// <param name="extendColumns">不适用通用的字段</param>
        /// <returns></returns>
        private string generateSqlCondition<T>(T query, List<string> extendColumns)
        {
            PropertyInfo[] props = typeof(T).GetProperties().Where(o => !extendColumns.Contains(o.Name)).ToArray();
            var condition = "";
            foreach (var prop in props)
            {
                var val = prop.GetValue(query, null);
                if (val != null)
                {
                    if (prop.PropertyType == typeof(string))
                    {
                        if (!string.IsNullOrEmpty(val.ToString()))
                        {
                            condition += "( ";
                            var cond = val.ToString().Trim();
                            //以分号结尾的说明来自分组数据，不使用like 而是使用=
                            if (cond.EndsWith(";;;"))
                            {
                                var conditions = cond.TrimEnd(new char[] { ';', ';', ';' }).Split(';');
                                foreach (var item in conditions)
                                {
                                    condition += " " + prop.Name + " = " + "'" + item + "' or ";
                                }
                            }
                            else
                            {
                                condition += " " + prop.Name + " like " + "'%" + cond + "%' or ";
                            }


                            condition = condition.Trim().TrimEnd(new char[] { 'o', 'r' });
                            condition += " ) and ";
                        }

                    }
                    else if (prop.PropertyType == typeof(decimal))
                    {
                        if (((decimal)val) != 0)
                        {
                            condition += " " + prop.Name + "=" + val + " and ";
                        }

                    }
                }
            }

            return condition.Trim().TrimEnd('d').TrimEnd('n').TrimEnd('a');

        }

        /// <summary>
        ///  处理原来的数据格式
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="extendColumns"></param>
        /// <returns></returns>
        private string generateBasicSql(List<ReportViewColumn> columns, List<string> extendColumns)
        {
            var sql = "select ";
            if (extendColumns == null) extendColumns = new List<string>();
            foreach (var item in columns)
            {
                if (!string.IsNullOrEmpty(item.Field) && !extendColumns.Contains(item.Field))
                {
                    if (!string.IsNullOrEmpty(item.FieldFormat))
                    {
                        sql += item.FieldFormat + ", ";
                    }
                    else
                    {
                        sql += item.Field + ", ";
                    }
                }
            }

            return sql.Trim().TrimEnd(',');
        }

        /// <summary>
        /// 生成最后的查询语句
        /// </summary>
        /// <param name="basicSql"></param>
        /// <param name="condition"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        private string generateSql(string basicSql, string condition, string order, string sort, int? pageIndex, int? pageSize)
        {
            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }

            var orderCondition = " order by " + sort + " " + orderName;

            //---0:basicSql
            //---1:condition
            //---2:orderCondition
            //---3:where rownum <=endIndex
            //---4:where rn >=beginIndex
            var sql = "select * from (select t.*,rownum as rn from (select * from ( {0})  {1}   {2} ) t {3} ) {4}";

            if (pageIndex.HasValue && pageSize.HasValue)
            {
                int beginIndex = (pageIndex.Value - 1) * pageSize.Value + 1;
                int endIndex = beginIndex + pageSize.Value - 1;
                sql = string.Format(sql, basicSql, condition, orderCondition, " where rownum <= " + endIndex, " where rn >= " + beginIndex);
            }
            else
            {
                sql = string.Format(sql, basicSql, condition, orderCondition, "", "");
            }

            return sql;
        }

        /// <summary>
        /// 生成nvl字段
        /// </summary>
        /// <param name="nvlCount"></param>
        /// <param name="nvlTip"></param>
        /// <returns></returns>
        private string generateNVL(string nvlName, string groupName, decimal nvlCount, string nvlTip)
        {
            if (nvlCount == 1)
            {
                nvlName = "nvl(" + nvlTip + "." + groupName + ",";
            }
            else if (nvlCount == 2)
            {
                nvlName += nvlTip + "." + groupName + "),";
            }
            else
            {
                nvlName = "nvl(" + nvlName + nvlTip + "." + groupName + "),";
            }

            return nvlName;
        }
        #endregion

    }
}
